package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.DesignerDao;
import com.cn.wanxi.model.DesignerModel;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DesignerImpl implements DesignerDao {
    @Override
    public List<DesignerModel> findAll(DesignerModel designerModel) {
        String sql = "select * from designer where 1=1 ";
        sql += setSql(designerModel);
        sql += " order by id desc ";
        sql += "limit " + (designerModel.getPageNo() - 1) * designerModel.getPageSize() + ", " + designerModel.getPageSize() + "";
        ResultSet resultSet = JDBC.query(sql);
        List<DesignerModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                DesignerModel model = new DesignerModel();
                model.setId(resultSet.getInt("id"));
                model.setDesignername(resultSet.getString("designer_name"));
                model.setPhone(resultSet.getString("phone"));
                model.setCreateTime(resultSet.getString("create_time"));
                model.setEmail(resultSet.getString("email"));
                model.setEnable(resultSet.getInt("enable"));
                model.setUpdateTime(resultSet.getString("update_time"));
                model.setSex(resultSet.getString("sex"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int add(DesignerModel designerModel) {
        String sql = "INSERT INTO designer(designer_name, img_href, introduce, email, phone, sex, create_time, update_time, enable)" +
                " VALUES( ?, ?, ?, ?, ?, ?, now(), now(), ?);\n";
        List list = new ArrayList();
        list.add(designerModel.getDesignername());
        list.add(designerModel.getImgHref());
        list.add(designerModel.getIntroduce());
        list.add(designerModel.getEmail());
        list.add(designerModel.getPhone());
        list.add(designerModel.getSex());
        list.add(designerModel.getEnable());
        return JDBC.updatea(sql, list);
    }

    @Override
    public int update(DesignerModel designerModel) {
        String sql = "UPDATE `designer` SET ";
        if (designerModel.getDesignername() != null && !"".equals(designerModel.getDesignername())) {
            sql += "`designer_name` = '" + designerModel.getDesignername() + "', ";
        }
        if (designerModel.getSex() != null && !"".equals(designerModel.getSex())) {
            sql += "`sex` = '" + designerModel.getSex() + "', ";
        }
        if (designerModel.getPhone() != null && !"".equals(designerModel.getPhone())) {
            sql += "`phone` = '" + designerModel.getPhone() + "',";
        }
        if (designerModel.getEmail() != null && !"".equals(designerModel.getEmail())) {
            sql += "`email` = '" + designerModel.getEmail() + "', ";
        }
        if (designerModel.getIntroduce() != null && !"".equals(designerModel.getIntroduce())) {
            sql += "`introduce` = '" + designerModel.getIntroduce() + "', ";
        }
        if (designerModel.getImgHref() != null && !"".equals(designerModel.getImgHref())) {
            sql += "`img_href` = '" + designerModel.getImgHref() + "', ";
        }
        if (designerModel.getEnable() != null) {
            sql += "`enable` = '" + designerModel.getEnable() + "', ";
        }
        sql += " `update_time` = now()";
        sql += " WHERE (`id` = '" + designerModel.getId() + "');";
        return JDBC.update(sql);
    }

    @Override
    public DesignerModel findById(DesignerModel designerModel) {
        String sql = "select * from designer where id =" + designerModel.getId();
        ResultSet resultSet = JDBC.query(sql);
        DesignerModel model = null;
        try {
            while (resultSet.next()) {
                model = new DesignerModel();
                model.setId(resultSet.getInt("id"));
                model.setDesignername(resultSet.getString("designer_name"));
                model.setIntroduce(resultSet.getString("introduce"));
                model.setPhone(resultSet.getString("phone"));
                model.setCreateTime(resultSet.getString("create_time"));
                model.setEmail(resultSet.getString("email"));
                model.setEnable(resultSet.getInt("enable"));
                model.setUpdateTime(resultSet.getString("update_time"));
                model.setSex(resultSet.getString("sex"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public int delete(DesignerModel designerModel) {
        String sql = "delete from designer where id =" + designerModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public int getCount(DesignerModel designerModel) {
        String sql = "select count(*) count from designer where 1=1 ";
        sql += setSql(designerModel);
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                return resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    private String setSql(DesignerModel designerModel) {
        String sql = "";
        if (!"".equals(designerModel.getDesignername())) {
            sql += " and designer_name like '%" + designerModel.getDesignername() + "%'";
        }
        if (!"".equals(designerModel.getPhone())) {
            sql += " and phone like '%" + designerModel.getPhone() + "%'";
        }
        if (!"".equals(designerModel.getSex())) {
            sql += " and sex = '" + designerModel.getSex() + "'";
        }
        if (!"".equals(designerModel.getStart()) && "".equals(designerModel.getEnd())) {
            sql += " and birthday >='" + designerModel.getStart() + "'";
        }
        if ("".equals(designerModel.getStart()) && !"".equals(designerModel.getEnd())) {
            sql += " and birthday <='" + designerModel.getEnd() + "'";
        }
        if (!"".equals(designerModel.getStart()) && !"".equals(designerModel.getEnd())) {
            sql += " and birthday between '" + designerModel.getStart() + "' and '" + designerModel.getEnd() + "'";
        }
        if (designerModel.getEnable() != -1) {
            sql += " and enable=" + designerModel.getEnable();
        }
//        爱好模糊查询
//        select * from user where username='fsadfas' or '1'='1';
//        sql注入的bug
//        select * from user where username=?;
        return sql;
    }

    @Override
    public List<DesignerModel> getAboutDesigner() {
        String sql = "select * from designer where enable = 1 and is_exhibition = 1 order by id asc limit 4";
        ResultSet resultSet = JDBC.query(sql);
        List<DesignerModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                DesignerModel model = new DesignerModel();
                model.setId(resultSet.getInt("id"));
                model.setImgHref(resultSet.getString("img_href"));
                model.setDesignername(resultSet.getString("designer_name"));
                model.setIntroduce(resultSet.getString("introduce"));
                model.setPhone(resultSet.getString("phone"));
                model.setCreateTime(resultSet.getString("create_time"));
                model.setEmail(resultSet.getString("email"));
                model.setEnable(resultSet.getInt("enable"));
                model.setUpdateTime(resultSet.getString("update_time"));
                model.setSex(resultSet.getString("sex"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
